#include "DatabaseService.h"
#include <QCoreApplication>
#include <QDir>

// 静态成员初始化
DatabaseService *DatabaseService::s_instance = nullptr;
QMutex DatabaseService::s_mutex;

DatabaseService::DatabaseService(QObject *parent)
    : QObject(parent), m_initialized(false)
{
}

DatabaseService::~DatabaseService()
{
    closeDatabase();
}

// 获取单例实例
DatabaseService *DatabaseService::instance()
{
    if (s_instance == nullptr)
    {
        QMutexLocker locker(&s_mutex);
        if (s_instance == nullptr)
        {
            s_instance = new DatabaseService();
        }
    }
    return s_instance;
}

// 初始化数据库
bool DatabaseService::initialize(const QString &dbPath)
{
    if (m_initialized)
    {
        qWarning() << "数据库已初始化";
        return true;
    }

    // 1. 创建数据库连接
    m_db = QSqlDatabase::addDatabase("QSQLITE");
    m_db.setDatabaseName(dbPath);
    m_dbPath = dbPath;

    if (!m_db.open())
    {
        QString error = QString("数据库打开失败: %1").arg(m_db.lastError().text());
        qCritical() << error;
        emit errorOccurred(error);
        emit databaseInitialized(false);
        return false;
    }

    qInfo() << "数据库文件:" << dbPath;

    // 2. 数据库优化配置
    if (!configureDatabaseOptimization())
    {
        qWarning() << "数据库优化配置失败";
    }

    // 3. 创建所有表
    if (!createTables())
    {
        emit databaseInitialized(false);
        return false;
    }

    // 4. 创建索引
    if (!createIndexes())
    {
        qWarning() << "部分索引创建失败";
    }

    // 5. 创建视图
    if (!createViews())
    {
        qWarning() << "视图创建失败";
    }

    // 6. 初始化默认配置
    if (!initializeDefaultConfig())
    {
        qWarning() << "默认配置初始化失败";
    }

    m_initialized = true;
    qInfo() << "✅ 数据库初始化完成";
    emit databaseInitialized(true);

    return true;
}

// 关闭数据库
void DatabaseService::closeDatabase()
{
    if (m_db.isOpen())
    {
        m_db.close();
        qInfo() << "数据库连接已关闭";
    }
    m_initialized = false;
}

// 创建数据库表
bool DatabaseService::createTables()
{
    qInfo() << "开始创建数据库表...";

    // 表 1：设备信息表
    if (!executeSql(R"(
        CREATE TABLE IF NOT EXISTS devices (
            device_id TEXT PRIMARY KEY,
            device_name TEXT NOT NULL,
            ip_address TEXT NOT NULL,
            port INTEGER DEFAULT 502,
            slave_id INTEGER DEFAULT 1,
            status TEXT DEFAULT 'offline',
            last_update DATETIME,
            created_at DATETIME DEFAULT (datetime('now', 'localtime')),
            description TEXT
        )
    )",
                    "创建设备信息表"))
    {
        return false;
    }

    // 表 2：实时数据表
    if (!executeSql(R"(
        CREATE TABLE IF NOT EXISTS realtime_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            device_id TEXT NOT NULL,
            timestamp DATETIME DEFAULT (datetime('now', 'localtime')),
            temperature REAL,
            pressure REAL,
            flow REAL,
            FOREIGN KEY (device_id) REFERENCES devices(device_id)
        )
    )",
                    "创建实时数据表"))
    {
        return false;
    }

    // 表 3：分钟统计表
    if (!executeSql(R"(
        CREATE TABLE IF NOT EXISTS minute_statistics (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            device_id TEXT NOT NULL,
            timestamp DATETIME NOT NULL,
            temp_avg REAL,
            temp_max REAL,
            temp_min REAL,
            pressure_avg REAL,
            pressure_max REAL,
            pressure_min REAL,
            flow_avg REAL,
            flow_max REAL,
            flow_min REAL,
            sample_count INTEGER DEFAULT 60,
            FOREIGN KEY (device_id) REFERENCES devices(device_id)
        )
    )",
                    "创建分钟统计表"))
    {
        return false;
    }

    // 表 4：小时统计表
    if (!executeSql(R"(
        CREATE TABLE IF NOT EXISTS hour_statistics (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            device_id TEXT NOT NULL,
            timestamp DATETIME NOT NULL,
            temp_avg REAL,
            temp_max REAL,
            temp_min REAL,
            pressure_avg REAL,
            pressure_max REAL,
            pressure_min REAL,
            flow_avg REAL,
            flow_max REAL,
            flow_min REAL,
            sample_count INTEGER DEFAULT 60,
            FOREIGN KEY (device_id) REFERENCES devices(device_id)
        )
    )",
                    "创建小时统计表"))
    {
        return false;
    }

    // 表 5：报警与事件记录表
    if (!executeSql(R"(
        CREATE TABLE IF NOT EXISTS alarm_records (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            device_id TEXT NOT NULL,
            timestamp DATETIME DEFAULT (datetime('now', 'localtime')),
            alarm_type TEXT NOT NULL,
            alarm_level TEXT DEFAULT 'warning',
            param_name TEXT,
            param_value REAL,
            threshold REAL,
            description TEXT,
            acknowledged INTEGER DEFAULT 0,
            acknowledged_by TEXT,
            acknowledged_at DATETIME,
            FOREIGN KEY (device_id) REFERENCES devices(device_id)
        )
    )",
                    "创建报警记录表"))
    {
        return false;
    }

    // 表 6：系统配置表
    if (!executeSql(R"(
        CREATE TABLE IF NOT EXISTS system_config (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            config_key TEXT UNIQUE NOT NULL,
            config_value TEXT NOT NULL,
            config_type TEXT DEFAULT 'string',
            description TEXT,
            updated_at DATETIME DEFAULT (datetime('now', 'localtime'))
        )
    )",
                    "创建系统配置表"))
    {
        return false;
    }

    // 表 7：操作日志表
    if (!executeSql(R"(
        CREATE TABLE IF NOT EXISTS operation_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp DATETIME DEFAULT (datetime('now', 'localtime')),
            user_name TEXT,
            operation_type TEXT NOT NULL,
            target_object TEXT,
            description TEXT,
            result TEXT DEFAULT 'success',
            error_message TEXT
        )
    )",
                    "创建操作日志表"))
    {
        return false;
    }

    qInfo() << "✅ 所有表创建完成";
    return true;
}

// 创建索引
bool DatabaseService::createIndexes()
{
    qInfo() << "开始创建索引...";

    // 设备表索引
    executeSql("CREATE INDEX IF NOT EXISTS idx_devices_status ON devices(status)", "设备状态索引");

    // 实时数据表索引
    executeSql("CREATE INDEX IF NOT EXISTS idx_realtime_device_time ON realtime_data(device_id, timestamp DESC)", "实时数据复合索引");
    executeSql("CREATE INDEX IF NOT EXISTS idx_realtime_timestamp ON realtime_data(timestamp)", "实时数据时间索引");

    // 分钟统计表索引
    executeSql("CREATE INDEX IF NOT EXISTS idx_minute_device_time ON minute_statistics(device_id, timestamp DESC)", "分钟统计复合索引");
    executeSql("CREATE UNIQUE INDEX IF NOT EXISTS idx_minute_unique ON minute_statistics(device_id, timestamp)", "分钟统计唯一索引");

    // 小时统计表索引
    executeSql("CREATE INDEX IF NOT EXISTS idx_hour_device_time ON hour_statistics(device_id, timestamp DESC)", "小时统计复合索引");
    executeSql("CREATE UNIQUE INDEX IF NOT EXISTS idx_hour_unique ON hour_statistics(device_id, timestamp)", "小时统计唯一索引");

    // 报警记录表索引
    executeSql("CREATE INDEX IF NOT EXISTS idx_alarm_device_time ON alarm_records(device_id, timestamp DESC)", "报警设备时间索引");
    executeSql("CREATE INDEX IF NOT EXISTS idx_alarm_level ON alarm_records(alarm_level)", "报警级别索引");
    executeSql("CREATE INDEX IF NOT EXISTS idx_alarm_acknowledged ON alarm_records(acknowledged)", "报警确认状态索引");

    // 操作日志表索引
    executeSql("CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON operation_logs(timestamp DESC)", "操作日志时间索引");

    qInfo() << "✅ 索引创建完成";
    return true;
}

// 创建视图
bool DatabaseService::createViews()
{
    qInfo() << "开始创建视图...";

    // 视图 1：设备实时状态视图
    bool success = executeSql(R"(
        CREATE VIEW IF NOT EXISTS v_device_realtime_status AS
        SELECT 
            d.device_id,
            d.device_name,
            d.ip_address,
            d.status,
            rd.timestamp AS last_data_time,
            rd.temperature,
            rd.pressure,
            rd.flow
        FROM devices d
        LEFT JOIN (
            SELECT device_id, timestamp, temperature, pressure, flow,
                   ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY timestamp DESC) as rn
            FROM realtime_data
        ) rd ON d.device_id = rd.device_id AND rd.rn = 1
    )",
                              "创建设备实时状态视图");

    if (success)
    {
        qInfo() << "✅ 视图创建完成";
    }

    return success;
}

// 初始化默认配置
bool DatabaseService::initializeDefaultConfig()
{
    qInfo() << "开始初始化默认配置...";

    QSqlQuery query(m_db);
    query.prepare("INSERT OR IGNORE INTO system_config (config_key, config_value, config_type, description) VALUES (?, ?, ?, ?)");

    // 配置数据（键、值、类型、描述）
    QList<QStringList> configs = {
        {"poll_interval", "1000", "int", "数据采集周期（毫秒）"},
        {"temp_warning_threshold", "80.0", "float", "温度警告阈值（℃）"},
        {"temp_critical_threshold", "100.0", "float", "温度严重报警阈值（℃）"},
        {"pressure_warning_threshold", "500.0", "float", "压力警告阈值（kPa）"},
        {"pressure_critical_threshold", "800.0", "float", "压力严重报警阈值（kPa）"},
        {"flow_warning_threshold", "100.0", "float", "流量警告阈值（L/min）"},
        {"realtime_data_retention_days", "7", "int", "实时数据保留天数"},
        {"minute_data_retention_days", "30", "int", "分钟统计保留天数"},
        {"hour_data_retention_days", "365", "int", "小时统计保留天数"},
        {"auto_cleanup_enabled", "1", "bool", "是否启用自动清理"},
        {"cleanup_time", "02:00", "string", "自动清理时间（HH:MM）"}};

    int insertedCount = 0;
    for (const auto &config : configs)
    {
        query.addBindValue(config[0]); // config_key
        query.addBindValue(config[1]); // config_value
        query.addBindValue(config[2]); // config_type
        query.addBindValue(config[3]); // description

        if (query.exec())
        {
            if (query.numRowsAffected() > 0)
            {
                insertedCount++;
            }
        }
        else
        {
            qWarning() << "配置插入失败:" << config[0] << query.lastError().text();
        }
    }

    qInfo() << QString("✅ 默认配置初始化完成（新增 %1 项）").arg(insertedCount);
    return true;
}

// 配置数据库优化
bool DatabaseService::configureDatabaseOptimization()
{
    qInfo() << "配置数据库优化参数...";

    // WAL 模式（提升并发性能）
    executeSql("PRAGMA journal_mode=WAL", "启用 WAL 模式");

    // 同步模式（平衡性能和安全性）
    executeSql("PRAGMA synchronous=NORMAL", "设置同步模式");

    // 缓存大小（2000 页 × 4KB = 8MB）
    executeSql("PRAGMA cache_size=2000", "设置缓存大小");

    // 启用外键约束
    executeSql("PRAGMA foreign_keys=ON", "启用外键约束");

    qInfo() << "✅ 数据库优化配置完成";
    return true;
}

// 执行 SQL 语句
bool DatabaseService::executeSql(const QString &sql, const QString &description)
{
    QSqlQuery query(m_db);
    if (!query.exec(sql))
    {
        QString error = QString("%1 失败: %2").arg(description, query.lastError().text());
        qCritical() << error;
        emit errorOccurred(error);
        return false;
    }

    if (!description.isEmpty())
    {
        qDebug() << "  ✓" << description;
    }

    return true;
}
